package com.quanquan.store.repository;

import com.quanquan.store.entity.Store;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * 功能描述
 *
 * @ClassName:商户dao
 * @Author:xiongjunbao
 * @Date:2019-03-11 15:29
 * @Version: V1.0
 */
public interface StoreRepository extends JpaRepository<Store, String>, JpaSpecificationExecutor<Store> {


    /**
     * 根据商户id查找对应商户
     *
     * @param id
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE id = ?1 AND status_remove = 0 AND status_show = ?2 and store_status = ?3", nativeQuery = true)
    Store findByIdAndStatusShow(String id, Integer statusShow, Integer storeStatus);

    /**
     * 根据楼层查找对应商户列表
     *
     * @param count
     * @param size
     * @param floor
     * @return
     */
    @Query(value = "SELECT * FROM oo_store  WHERE status_show = 0 AND status_remove = 0 and store_status = 1 and floor = ?3 ORDER BY sort_order ASC limit ?1,?2", nativeQuery = true)
    List<Store> queryListByFloor(Integer count, Integer size, Integer floor);

    /**
     * 模糊查询商户列表
     *
     * @param count
     * @param size
     * @param storeName
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE LOCATE(?3,store_name)>0 and status_show = 0 AND status_remove = 0 limit ?1,?2", nativeQuery = true)
    List<Store> queryListByStoreName(Integer count, Integer size, String storeName);


    /**
     * 后台模糊查询商户列表
     *
     * @param count
     * @param size
     * @param storeName
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE LOCATE(?3,store_name)>0 AND status_remove = 0 limit ?1,?2", nativeQuery = true)
    List<Store> queryBackListByStoreName(Integer count, Integer size, String storeName);


    /**
     * 根据商户id查找对应商户
     *
     * @param id
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE id = ?1 AND status_remove = 0", nativeQuery = true)
    Store findByStoreId(String id);

    /**
     * 根据商户号查找对应商户
     *
     * @param storeNum
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE store_num = ?1 AND status_remove = 0", nativeQuery = true)
    Store findByStoreNum(String storeNum);

    /**
     * 根据商户id查找对应商户
     *
     * @param id
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE id = ?1", nativeQuery = true)
    Store findByStoreId1(String id);

    /**
     * 根据楼层查找对应商户
     *
     * @param floorName
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE store_floor = ?1 AND status_remove = 0  AND status_show = 0 order by sort_order limit ?2 ,?3", nativeQuery = true)
    List<Store> findByStoreFloor(String floorName, Integer count, Integer size);

    @Query(value = "SELECT * FROM oo_store WHERE store_floor = ?1 AND status_remove = 0  AND status_show = 0 order by sort_order", nativeQuery = true)
    List<Store> findByStoreFloor(String floorName);


    /**
     * 通过标签查找商户
     *
     * @param labelId
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_store s INNER JOIN oo_store_label sl WHERE s.status_remove = 0  AND s.status_show = 0 AND sl.store_id = s.id AND sl.label_id = ?1 AND sl.status_remove = 0 AND sl.status_show = 0 ORDER BY sort_order limit ?2,?3", nativeQuery = true)
    List<Store> findByLabelId(String labelId, Integer count, Integer size);

    /**
     * 根据业态查找对应商户
     *
     * @param name
     * @param count
     * @param size
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE LOCATE(?1,store_commercial)>0 AND status_remove = 0  AND status_show = 0 ORDER BY sort_order limit ?2 ,?3", nativeQuery = true)
    List<Store> findByStoreCommercial(String name, Integer count, Integer size);

    @Query(value = "SELECT s.* FROM oo_store s INNER JOIN oo_store_label sl WHERE LOCATE(?1,s.store_commercial)>0 AND s.store_floor = ?2 AND s.status_remove = 0  AND s.status_show = 0 AND sl.store_id = s.id AND sl.label_id = ?3 AND sl.status_remove = 0 AND sl.status_show = 0 ORDER BY s.sort_order limit ?4,?5", nativeQuery = true)
    List<Store> findByCommercialAndFloorAndLabelId(String commercialName, String floorName, String labelId, Integer count, Integer size);

    /**
     * 当楼层为空时
     *
     * @param name
     * @param count
     * @param size
     * @param labelId
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_store s INNER JOIN oo_store_label sl WHERE LOCATE(?1,s.store_commercial)>0 AND s.status_remove = 0  AND s.status_show = 0 AND sl.store_id = s.id AND sl.label_id = ?4 AND sl.status_remove = 0 AND sl.status_show = 0 ORDER BY s.sort_order limit ?2 ,?3", nativeQuery = true)
    List<Store> findByCommercialAndLabelId(String name, Integer count, Integer size, String labelId);

    /**
     * 当业态为空时
     *
     * @param name
     * @param count
     * @param size
     * @param labelId
     * @return
     */
    @Query(value = "SELECT s.* FROM oo_store s INNER JOIN oo_store_label sl WHERE store_floor = ?1 AND s.status_remove = 0  AND s.status_show = 0 AND sl.store_id = s.id AND sl.label_id = ?4 AND sl.status_remove = 0 AND sl.status_show = 0 ORDER BY s.sort_order limit ?2 ,?3", nativeQuery = true)
    List<Store> findByFloorAndLabelId(String name, Integer count, Integer size, String labelId);


    @Query(value = "SELECT * FROM oo_store WHERE LOCATE(?1,store_commercial)>0 AND store_floor = ?2 AND status_remove = 0  AND status_show = 0 ORDER BY sort_order limit ?3 ,?4", nativeQuery = true)
    List<Store> findByStoreCommercialAndFloor(String commercialName, String floorName, Integer count, Integer size);

    @Query(value = "SELECT * FROM oo_store WHERE status_remove = 0  AND status_show = 0 ORDER BY sort_order limit ?1 ,?2", nativeQuery = true)
    List<Store> findStore(Integer count, Integer size);

    @Query(value = "SELECT * FROM oo_store WHERE commercial_id = ?1 AND status_remove = 0  AND status_show = 0", nativeQuery = true)
    List<Store> findByCommercialId(String commercialId);

    @Query(value = "SELECT * FROM oo_store WHERE status_remove = 0 ORDER BY sort_order", nativeQuery = true)
    List<Store> findStoreList();

    /**
     * 通过数采id查找商户
     * @param shopEntityId
     * @return
     */
    @Query(value = "SELECT * FROM oo_store WHERE status_remove = 0 and shop_entity_id = ?1", nativeQuery = true)
    Store findShopEntityId(String shopEntityId);
}